﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using KVB.Common;

namespace Customer_Profile.eCard.DBDao
{
    public class SaleAgentDao
    {
        public static DataSet getSaleAgentList(string if_all)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@if_all", SqlDbType.VarChar, 50) };
                parameters[0].Value = if_all;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_SaleAgent_byall",parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getSaleAgentList", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getReturnCardList()
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                string str_sql = "SELECT return_id,card_no,attach_url,CASE attach_url WHEN '' THEN '' ELSE '查看'  END AS attach_name,user_id,user_name,return_date,remark FROM dbo.card_return";
                ds = DB.ExecuteDataset(conn, CommandType.Text, str_sql);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getReturnCardList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getChangeCardList()
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                string str_sql = "SELECT change_id,oldcard_no,newcard_no,attach_url,CASE attach_url WHEN '' THEN '' ELSE '查看'  END AS attach_name,user_id,user_name,change_date,remark FROM dbo.card_change";
                ds = DB.ExecuteDataset(conn, CommandType.Text, str_sql);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getChangeCardList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getSettleAgentList(string if_all)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@if_all", SqlDbType.VarChar, 50) };
                parameters[0].Value = if_all;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_SettleAgent_byall", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getSettleAgentList", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static bool AgentProdAdd(
                 int sale_agent_id,
                 string sale_agent_prod,
                 float agent_discount
           )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {   
                                                new SqlParameter("@sale_agent_id", SqlDbType.Int) ,
                                                new SqlParameter("@prod_code", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@agent_discount", SqlDbType.Float)                                                 
                                            };
                parameters[0].Value = sale_agent_id;
                parameters[1].Value = sale_agent_prod;
                parameters[2].Value = agent_discount;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_AgentProd_Add", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.EntityAdd", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static bool EntityAdd(
                  string entity_code,
                  string entity_name ,
                  string phone,
                  string fax,
                  string address,
                  string email,
                  string remark,
                  string in_use
            )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {   
                                                new SqlParameter("@entity_code", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@entity_name", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@phone", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@fax", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@address", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@email", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@remark", SqlDbType.VarChar,500) ,
                                                new SqlParameter("@in_use", SqlDbType.VarChar,50)                                                 
                                            };
                parameters[0].Value = entity_code;
                parameters[1].Value = entity_name ;
                parameters[2].Value = phone;
                parameters[3].Value = fax;
                parameters[4].Value = address;
                parameters[5].Value = email;
                parameters[6].Value = remark;
                parameters[7].Value = in_use;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Entity_Add", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.EntityAdd", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static bool SpreadRatioUpdate(
                  decimal d_ratio
            )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {   
                                                new SqlParameter("@reward_ratio", SqlDbType.Float)                                                                                           
                                            };
                parameters[0].Value = d_ratio;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Spread_Update", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.SpreadRatioUpdate", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static string getSpreadRatio()
        {
            SqlConnection conn = null;
            DataSet ds = null;
            string s_ratio="";
            try
            {
                DB.Connection(ref conn);               
                ds = DB.ExecuteDataset(conn, CommandType.Text, "SELECT reward_ratio FROM spread_rewardRatio");
                s_ratio = ds.Tables[0].Rows[0][0].ToString();
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getSpreadRatio", e.Message);
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return s_ratio;
        }

        public static bool SaleAgentAdd(
                  string sale_agent_name,
                  string linkman,
                  string phone,
                  string email,
                  string address,
                  string saleclass,
                  string remark,
                  string in_use
            )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {   
                                                new SqlParameter("@sale_agent_name", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@linkman", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@phone", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@email", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@address", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@saleclass", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@remark", SqlDbType.VarChar,500) ,
                                                new SqlParameter("@in_use", SqlDbType.VarChar,50)                                                 
                                            };
                parameters[0].Value = sale_agent_name;
                parameters[1].Value = linkman;
                parameters[2].Value = phone;
                parameters[3].Value = email;
                parameters[4].Value = address;
                parameters[5].Value = saleclass;
                parameters[6].Value = remark;
                parameters[7].Value = in_use;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_SaleAgent_Add", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ProductDao.ProductAdd", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static bool SettleAgentAdd(
                  string settle_agent_name,
                  string linkman,
                  string phone,
                  string email,
                  string address,
                  //string saleclass,
                  string remark,
                  string in_use
            )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {
                                                new SqlParameter("@settle_agent_name", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@linkman", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@phone", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@email", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@address", SqlDbType.VarChar,250) ,                                                
                                                new SqlParameter("@remark", SqlDbType.VarChar,500) ,
                                                new SqlParameter("@in_use", SqlDbType.VarChar,50)                                                 
                                            };
                parameters[0].Value = settle_agent_name;
                parameters[1].Value = linkman;
                parameters[2].Value = phone;
                parameters[3].Value = email;
                parameters[4].Value = address;                
                parameters[5].Value = remark;
                parameters[6].Value = in_use;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_SettleAgent_Add", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ProductDao.ProductAdd", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static bool AgentProdUpdate(
                 int id,
                 int sale_agent_id,
                 string sale_agent_prod,
                 float agent_discount
          )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {   
                                                new SqlParameter("@id", SqlDbType.Int),
                                                new SqlParameter("@sale_agent_id", SqlDbType.Int),
                                                new SqlParameter("@prod_code", SqlDbType.VarChar, 250),
                                                new SqlParameter("@agent_discount", SqlDbType.Float)
                                            };
                parameters[0].Value = id;
                parameters[1].Value = sale_agent_id;
                parameters[2].Value = sale_agent_prod;
                parameters[3].Value = agent_discount;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_AgentProd_Update", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.AgentProdUpdate", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static bool EntityUpdate(
                  int entity_id,
                  string entity_code,
                  string entity_name,
                  string phone,
                  string fax,
                  string address,
                  string email,
                  string remark,
                  string in_use
           )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {   new SqlParameter("@entity_id", SqlDbType.Int) ,
                                                new SqlParameter("@entity_code", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@entity_name", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@phone", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@fax", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@address", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@email", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@remark", SqlDbType.VarChar,500) ,
                                                new SqlParameter("@in_use", SqlDbType.VarChar,50)                                                 
                                            };
                parameters[0].Value = entity_id;
                parameters[1].Value = entity_code;
                parameters[2].Value = entity_name;
                parameters[3].Value = phone;
                parameters[4].Value = fax;
                parameters[5].Value = address;
                parameters[6].Value = email;
                parameters[7].Value = remark;
                parameters[8].Value = in_use;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Entity_Update", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.EntityUpdate", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static bool SaleAgentUpdate(
                 int sale_agent_id,
                 string sale_agent_name,
                 string linkman,
                 string phone,
                 string email,
                 string address,
                 string saleclass,
                 string remark,
                 string in_use
           )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {   
                                                new SqlParameter("@sale_agent_id", SqlDbType.Int) ,
                                                new SqlParameter("@sale_agent_name", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@linkman", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@phone", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@email", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@address", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@saleclass", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@remark", SqlDbType.VarChar,500) ,
                                                new SqlParameter("@in_use", SqlDbType.VarChar,50)                                                 
                                            };
                parameters[0].Value = sale_agent_id;
                parameters[1].Value = sale_agent_name;
                parameters[2].Value = linkman;
                parameters[3].Value = phone;
                parameters[4].Value = email;
                parameters[5].Value = address;
                parameters[6].Value = saleclass;
                parameters[7].Value = remark;
                parameters[8].Value = in_use;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_SaleAgent_Update", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ProductDao.SaleAgentUpdate", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static bool SettleAgentUpdate(
                int settle_agent_id,
                string settle_agent_name,
                string linkman,
                string phone,
                string email,
                string address,                
                string remark,
                string in_use
          )  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {   
                                                new SqlParameter("@settle_agent_id", SqlDbType.Int) ,
                                                new SqlParameter("@settle_agent_name", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@linkman", SqlDbType.VarChar, 250) ,
                                                new SqlParameter("@phone", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@email", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@address", SqlDbType.VarChar,250) ,                                                
                                                new SqlParameter("@remark", SqlDbType.VarChar,500) ,
                                                new SqlParameter("@in_use", SqlDbType.VarChar,50)                                                 
                                            };
                parameters[0].Value = settle_agent_id;
                parameters[1].Value = settle_agent_name;
                parameters[2].Value = linkman;
                parameters[3].Value = phone;
                parameters[4].Value = email;
                parameters[5].Value = address;                
                parameters[6].Value = remark;
                parameters[7].Value = in_use;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_SettleAgent_Update", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ProductDao.SaleAgentUpdate", e.Message);
                bok = false;
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static void getCpartyIDbyDealno(int deal_no,out string cparty_id)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@deal_no", SqlDbType.Int) ,
                                                new SqlParameter("@cparty_id", SqlDbType.VarChar, 50) 
                                            };
                parameters[0].Value = deal_no;
                parameters[1].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Cparty_getid", parameters);
                cparty_id = parameters[1].Value.ToString();
            }
            catch (Exception e)
            {
                cparty_id = "";
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getCpartyIDbyDealno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet getEntitybyID(int entity_id)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@entity_id", SqlDbType.Int) 
                                            };
                parameters[0].Value = entity_id;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Entity_getbyid", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getEntitybyID", e.Message);
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getAgentProdbyID(int id)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@id", SqlDbType.Int) 
                                            };
                parameters[0].Value = id;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_AgentProd_getbyid", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getAgentProdbyID", e.Message);
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getSaleAgentbyID(int sale_agent_id)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@sale_agent_id", SqlDbType.Int) 
                                            };
                parameters[0].Value = sale_agent_id;
                
                ds =DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_SaleAgent_getbyid", parameters);                
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getCpartyIDbyDealno", e.Message);
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getSettleAgentbyID(int settle_agent_id)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@settle_agent_id", SqlDbType.Int) 
                                            };
                parameters[0].Value = settle_agent_id;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_SettleAgent_getbyid", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getSettleAgentbyID", e.Message);
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getSettleBankAccount(int cparty_id)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@cparty_id", SqlDbType.Int) };
                parameters[0].Value = cparty_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Cparty_Bankaccount", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getSettleBankAccount", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getSettleCommonBankAccount(int cparty_id)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@cparty_id", SqlDbType.Int) };
                parameters[0].Value = cparty_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Cparty_Common_Bankaccount", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getSettleBankAccount", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getSettleCommonBankAccountInfo(int bankaccount_id)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@bankaccount_id", SqlDbType.Int) };
                parameters[0].Value = bankaccount_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Cparty_Common_Bankaccountinfo", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getSettleBankAccount", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }


        public static DataSet getDealBalance(int deal_no)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@deal_no", SqlDbType.Int) };
                parameters[0].Value = deal_no;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Deals_getDealBalance", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getDealBalance", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getCardStockList(string card_no,string status)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@card_no", SqlDbType.VarChar,250) ,
                                                new SqlParameter("@if_used", SqlDbType.VarChar,20) 
                                            };
                parameters[0].Value = card_no;
                parameters[1].Value = status;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_cardStock_List",parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("SaleAgentDao.getCardStockList", e.Message);
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }
    }
}